package ext.tianma.bom.service.sqe;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import ext.tianma.bom.service.ShowDocHelper;
import ext.tianma.util.PropertiesUtil;
import wt.util.WTContext;
import wt.util.WTProperties;

public class SQEWriteExcel {
	
	public static String sheetName = "SQE报表查询";
	public static String[] title = {"序号","物料编码","物料名称","物料描述","是否专显","是否已经样品承认","供应商名称"};
	
	
	/**
	 * 将传入的数据导出至Excel
	 * @param jsonArray
	 * @param wb
	 * @return
	 * @throws JSONException
	 * @throws IOException
	 */
	public static String exportExcel(JSONArray jsonArray,
			HSSFWorkbook wb) throws JSONException, IOException {
		
		String domainValue = "codebase" + File.separator + "ext" + File.separator + "tianma"
				+ File.separator + "cfg" + File.separator + "TianmaPro.properties";
		PropertiesUtil ut = new PropertiesUtil(domainValue);
		String domain = ut.getValueByKey("system.domain");
		String folder = "TempExcel";
		WTProperties props = WTProperties.getLocalProperties();
		
		String filePath = props.getProperty("wt.codebase.location") + File.separator + "temp" + File.separator + folder + File.separator;
		File targetFile = new File(filePath);
		if (!targetFile.exists()) {
			targetFile.mkdirs();
		}
		String pathAndName = filePath + "SqePartReport.xls";
		System.out.println("----pathAndName----"+pathAndName);
		
		String realPath = folder + File.separator + "SqePartReport.xls";
		realPath = domain + File.separator + "temp" + File.separator + realPath;
		System.out.println("----realPath----"+realPath);
		
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if (wb == null) {
			wb = new HSSFWorkbook();
		}

		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
        

		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow row = sheet.createRow(0);

		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

		// 声明列对象
		HSSFCell cell = null;

		// 创建标题
		for (int i = 0; i < title.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}

		// 创建内容
		System.out.println("jsonArray================" + jsonArray.length());
		for (int i = 0; i < jsonArray.length(); i++) {
			HSSFRow tempRow = sheet.createRow(i + 1);
			JSONObject jsonObjectTemp = jsonArray.getJSONObject(i);
			System.out.println("jsonObjectTemp================" + jsonObjectTemp.length());
			for (int j = 0; j < jsonObjectTemp.length(); j++) {
				String tempCellName = row.getCell(j).getStringCellValue();
				System.out.println("tempCellName===============" + tempCellName);
				String tempValue = "";
				if("序号".equals(tempCellName)) {
					tempValue = String.valueOf(i+1);
				} else if ("物料编码".equals(tempCellName)) {
					tempValue = (String) jsonObjectTemp.get("excelNum");
				} else if ("物料名称".equals(tempCellName)) {
					tempValue = (String) jsonObjectTemp.get("partName");
				} else if ("视图".equals(tempCellName)) {
					tempValue = (String) jsonObjectTemp.get("viewName");
				} else if ("物料描述".equals(tempCellName)) {
					tempValue = (String) jsonObjectTemp.get("partDescription");
				} else if ("是否专显".equals(tempCellName)) {
					tempValue = (String) jsonObjectTemp.get("specialMonitor");
				} else if ("是否已经样品承认".equals(tempCellName)) {
					tempValue = (String) jsonObjectTemp.get("whetherPartRecognize");
				} else if ("供应商名称".equals(tempCellName)) {
					tempValue = (String) jsonObjectTemp.get("partSupplier");
				} 
				
				tempRow.createCell(j).setCellValue(tempValue);
			}

		}
		
		sheet.autoSizeColumn((short)0); //调整第一列宽度
        sheet.autoSizeColumn((short)1); //调整第二列宽度
        sheet.autoSizeColumn((short)2); //调整第三列宽度
        sheet.autoSizeColumn((short)3);
        sheet.autoSizeColumn((short)4);
        sheet.autoSizeColumn((short)5);
        sheet.autoSizeColumn((short)6);
        sheet.autoSizeColumn((short)7);

		FileOutputStream os = new FileOutputStream(pathAndName);
		wb.write(os);
		os.flush();
		os.close();
		return realPath;
	}
	
	/**
	 * 获取域名
	 * @return
	 */
		public static String getDomainValue(){
			
			String domainOpp = new String();
			InputStream istream = null;
			try{
				WTProperties wtp = WTProperties.getLocalProperties();
				String siteOrgFile = wtp.getProperty("wt.inf.container.SiteOrganization.file"
						, "wt/inf/container/SiteOrganization.properties");
				istream = WTContext.getContext().getResourceAsStream(siteOrgFile);
				wtp = new WTProperties(null);
				if(null!= istream){
					wtp.load(istream);
				}
				String siteDomainValue = wtp.getProperty("wt.inf.container.SiteOrganization.internetDomain");
				String[] domainSplit = siteDomainValue.split("[.]");
				domainOpp = domainSplit[0];
				for(int i=1;i<domainSplit.length;i++){
					domainOpp = domainSplit[i] + "." + domainOpp;
				}
			}catch(IOException e){
				e.printStackTrace();
			}finally{
				try{
					if(null!= istream){
						istream.close();
					}
				}catch(IOException e){
					e.printStackTrace();
				}
			}
			return domainOpp;
		}

}
